﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Net;

namespace O3SqlFileExecuter
{
    public class SqlFileExecuter
    {
        private string _FileUrl;
        private SqlConnection _DestinationConnection=null;
        private int _CommandTimeOut = 600;

        #region "Properties"
        public string FileUrl
        {
            set { _FileUrl = value; }
            get {return _FileUrl; }
        }
        public SqlConnection DestinationConnection
        {
            set { _DestinationConnection = value; }
            get { return _DestinationConnection; }
            
        }

        public int CommandTimeOut
        {
            set { _CommandTimeOut = value; }
            get { return _CommandTimeOut; }            
        }
        #endregion "Properties"

        public string Execute()
        {
            string Result;

            try
            {
                if (string.IsNullOrEmpty(_FileUrl) || _DestinationConnection == null)
                { throw new Exception("Invalid Input Parameters."); }

                // read file
                WebRequest request = WebRequest.Create(_FileUrl);
                using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
                {
                    _DestinationConnection.Open();

                    while (!sr.EndOfStream)
                    {
                        StringBuilder sb = new StringBuilder();
                        SqlCommand cmd = _DestinationConnection.CreateCommand();

                        while (!sr.EndOfStream)
                        {
                            string s = sr.ReadLine();
                            if (s != null && s.ToUpper().Trim().Equals("GO"))
                            {
                                break;
                            }

                            sb.AppendLine(s);
                        }

                        // Execute T-SQL against the target database
                        cmd.CommandText = sb.ToString();
                        cmd.CommandTimeout = _CommandTimeOut;

                        cmd.ExecuteNonQuery();                        
                    }

                    Result = "OK!!";
                }
            }
            catch (Exception ex)
            {
                Result = ex.Message;
            }
            finally
            {
                // Close out the connection
                //
                if (_DestinationConnection != null)
                {
                    try
                    {
                        _DestinationConnection.Close();
                        _DestinationConnection.Dispose();
                    }
                    catch (Exception e)
                    {
                        Result = e.Message;
                    }
                    
                }                
            }

            return Result;
        }

    }
}
